CData API ServerでViewをリソースに指定してみる

「カスタマーストーリー」を支えるツール特集

#MySQL

#REST

#Amazon Redshift

#AWS

#CData

kobayashi.m

2020.08.24

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

データアナリティクス事業本部のkobayashiです。

REST API構築ツール CData API Server を使ってAPIを構築した際に、「特定の値に絞り込んだのもをレスポンスで返したい」、「複数のテーブルを結合した上である条件を使って絞り込んだレスポンスを返したい」といった質問をよく受けます。

その際にはCData API Serverの「パラメータ」、「集計処理」、「リレーション」の機能を使えばおおよそ要求は満たせます。以下のエントリを確認していただけるとどの様に動作するのかがわかります。

またデータソースと同等のクエリを実行できるダイレクトクエリを使えばユーザーが自由に加工したデータを取得できます。

CData API Serverで作成したAPIでDirectqueryを使ってみる

ただユーザーに操作を任せるのではなく予めある程度データの形を決めておいてその形でレスポンスを返しという場合は他の方法で対応する必要があります。今回はこの内容をまとめたいと思います。

なおCData API Serverを使ったAPI構築は下記のエントリに詳細な内容がまとまっていますのでこちらも併せてご覧ください。

環境

Amazon Linux 2
CData API Server - 19.0.7493.0
RDS MySQL 8.0.15

Viewを使う

CData API Serverで構築したAPIで特定の値に絞り込んだものをレスポンスで返したい」、「複数のテーブルを結合した上である条件を使って絞り込んだレスポンスを返したい」場合の解は簡単で「View」を使うことで賄えます。専用のテーブルを作ってもいいと思いますが、専用のテーブルを作るにはそれ用のジョブを用意し実行するという作業が発生します。それよりも取得したいデータの形が決まっていてそれを取得するクエリも判明しているならViewを使えば簡単です。

CData API Serverではリソース（エンドポイント）を作成する際にデータソースのテーブルを指定しますがViewももちろん指定できます。

では実際にCData API ServerでViewでリソースが設定できるか事を確かめて見たいと思います。

MySQLで試してみる

MySQL公式から入手できるworld databaseを予めデータベースにインポートしておきます。登録すると

city
country
countrylanguage

の3つのテーブルが作成されます。これを使い人口1000000より多いASIAのcityを抽出するViewを定義します。

CREATE VIEW world.city_over1000000 AS
SELECT
    ci.ID AS ID,
    ci.Name AS Name,
    ci.CountryCode AS CountryCode,
    ci.District AS District,
    ci.Population AS Population,
    co.Name AS co_Name,
    co.Region AS co_Region,
    co.Population AS co_Population
FROM
    (world.city ci
JOIN world.country co ON
    (ci.CountryCode = co.Code
    AND co.Continent = 'ASIA'))
WHERE
    ci.Population > 1000000;

次にCData API Serverの画面で作成したViewでリソースを作成してみます。

設定 > リソース > リソースの追加 で設定したMySQLの接続を選択すると以下の様に作成したViewも表示されるのでチェックボックスを入れて次へを押下してリソースを作成します。

設定が終わると以下のようにリソースが作成されてAPIのエンドポイントとして機能するようになります。

確認

実際にリクエストを送ってレスポンスを確かめてみます。

https://{APIのアドレス}/api.rsc/world_city_over1000000/

以下のようなレスポンスが返り、Viewのデータが取得できています。

{
  "@odata.context": "http://{APIのアドレス}/api.rsc/$metadata#world_city_over1000000",
  "value": [
    {
      "co_Name": "Afghanistan",
      "co_Population": 22720000,
      "co_Region": "Southern and Central Asia",
      "CountryCode": "AFG",
      "District": "Kabol",
      "ID": 1,
      "Name": "Kabul",
      "Population": 1780000
    },
    {
      "co_Name": "Armenia",
      "co_Population": 3520000,
      "co_Region": "Middle East",
      "CountryCode": "ARM",
      "District": "Yerevan",
      "ID": 126,
      "Name": "Yerevan",
      "Population": 1248700
    },
    {
      "co_Name": "Azerbaijan",
      "co_Population": 7734000,
      "co_Region": "Middle East",
      "CountryCode": "AZE",
      "District": "Baki",
      "ID": 144,
      "Name": "Baku",
      "Population": 1787800
    },
...
  ]
}

Redshiftで試してみる

Redshiftの場合は通常のViewだけではなくMATERIALIZED Viewも使えるので両方試してみます。データはRedshiftのチュートリアルで作成できるデータをインポートしておきます。このチュートリアルの中に出てくるクエリ

Find events in the 99.9 percentile in terms of all time gross sales.
Find top 10 buyers by quantity.

を通常のViewとMATERIALIZED Viewにしてみます。

CREATE VIEW events_percentile AS
SELECT eventname, total_price 
FROM  (SELECT eventid, total_price, ntile(1000) over(order by total_price desc) as percentile 
       FROM (SELECT eventid, sum(pricepaid) total_price
             FROM   sales
             GROUP BY eventid)) Q, event E
       WHERE Q.eventid = E.eventid
       AND percentile = 1
       ;

CREATE MATERIALIZED VIEW top_100_buyers AS
SELECT firstname, lastname, total_quantity 
FROM   (SELECT buyerid, sum(qtysold) total_quantity
        FROM  sales
        GROUP BY buyerid
        ORDER BY total_quantity desc limit 100) Q, users
WHERE Q.buyerid = userid;

次にCData API Serverの画面で作成したViewでリソースを作成してみます。

設定が終わると以下のようにリソースが作成されてAPIのエンドポイントとして機能するようになります。

確認

実際にリクエストを送ってレスポンスを確かめてみます。

Viewの確認

https://{APIのアドレス}/api.rsc/public_events_percentile/

以下のようなレスポンスが返り、Viewのデータが取得できています。

{
  "@odata.context": "http://{APIのアドレス}/api.rsc/$metadata#public_events_percentile",
  "value": [
    {
      "eventname": "The Little Mermaid",
      "total_price": 49956.00
    },
    {
      "eventname": "Janet Jackson",
      "total_price": 51049.00
    },
    {
      "eventname": "Mary Poppins",
      "total_price": 46780.00
    },
    {
      "eventname": "Live",
      "total_price": 46661.00
    }
...
  ]
}

MATERIALIZED Viewの確認

https://{APIのアドレス}/api.rsc/public_top_100_buyers/

以下のようなレスポンスが返り、MATERIALIZED Viewのデータが取得できています。

{
  "@odata.context": "http://{APIのアドレス}/api.rsc/$metadata#public_top_100_buyers",
  "value": [
    {
      "firstname": "Chancellor",
      "lastname": "Odonnell",
      "total_quantity": 52
    },
    {
      "firstname": "Louis",
      "lastname": "Cooper",
      "total_quantity": 48
    },
    {
      "firstname": "Kadeem",
      "lastname": "Blair",
      "total_quantity": 60
    },
...
  ]
}